package Beans.EJB_Filters;

import Entities.RoomSearchEntity;
import Entities.RoomEntity;
import Entities.OrderCalendarEntity;

import javax.persistence.PersistenceContext;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.annotation.Resource;
import javax.ejb.SessionContext;
import javax.ejb.Stateless;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import java.util.List;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.UtilsPak.CommonFunctions;


/**
 * Created by IntelliJ IDEA.
 * User: mchebanenko
 * Date: 10-Jan-2011
 * Time: 3:28:04 PM
 * To change this template use File | Settings | File Templates.
 */
@Stateless
@TransactionManagement(TransactionManagementType.CONTAINER)
public class RoomListFltBean implements IRoomListFlt {

    @PersistenceContext(unitName="Hostel_persistence")
    private EntityManager entityManager;

    @Resource
    private SessionContext context;


    public List getRoomList(RoomListFltEntity flt){
        String select ="select DISTINCT ROOM.ROOM_ID,PLACES,ROOMS_QTY,ROOM.STYLE_ID,STYLE_NAME,COST";
        String from ="\nfrom ROOM \n" +
                "join ROOM_STYLE on ROOM.STYLE_ID=ROOM_STYLE.STYLE_ID\n" +
                "join ROOM_PRICES on ROOM.ROOM_ID=ROOM_PRICES.ROOM_ID\n";

        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMdd");
        String status="VALUE ";

        String where = "\n where 1=1 " ;

         if (flt.getStatus()!= null)
        {
            if (flt.getStatus().length >0)
            {

                if (CommonFunctions.isItemInArray(flt.getStatus(),2)){
                                    status =status.replace("VALUE","ISNULL(O2.STATUS_ID*0 + 2,VALUE)");
                    /* buzy rooms */from= from + "left join ORDERS O2 on ROOM.ROOM_ID=O2.ROOM_ID and O2.DATE_ENTER<='"+outputFormat.format(flt.getDateTo())+"' and O2.DATE_LEAVE>'"+outputFormat.format(flt.getDateFrom())+"' AND O2.STATUS_ID=2 \n";
                }
                if (CommonFunctions.isItemInArray(flt.getStatus(),3)){
                                     status =status.replace("VALUE","ISNULL(O3.STATUS_ID*0 + 3,VALUE)");
                    /* rezerved   */ from= from + "left join ORDERS O3 on ROOM.ROOM_ID=O3.ROOM_ID and O3.DATE_ENTER<='"+outputFormat.format(flt.getDateTo())+"' and O3.DATE_LEAVE>'"+outputFormat.format(flt.getDateFrom())+"' AND O3.STATUS_ID=1 \n";
               }
                if (CommonFunctions.isItemInArray(flt.getStatus(),4)){
                                     status =status.replace("VALUE","ISNULL(O4.STATUS_ID*0 + 4,VALUE)");
                    /* end term   */ from= from + "left join ORDERS O4 on ROOM.ROOM_ID=O4.ROOM_ID and O4.DATE_LEAVE between'"+outputFormat.format(flt.getDateFrom())+"' and '"+outputFormat.format(flt.getDateTo())+"' AND O4.STATUS_ID=2 \n";
                }

                if (CommonFunctions.isItemInArray(flt.getStatus(),1)){
                                     status =status.replace("VALUE","ISNULL(O1.STATUS_ID*0 + 1,1)");
                    /* free rooms */ from= from + "left join ORDERS O1 on ROOM.ROOM_ID=O1.ROOM_ID ";
                                     where =where +  " and NOT ((ISNULL(O1.DATE_ENTER,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"'))  BETWEEN '"+outputFormat.format(flt.getDateFrom())+"' AND '"+outputFormat.format(flt.getDateTo())+"' OR ISNULL(O1.DATE_LEAVE,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"'))  BETWEEN '"+outputFormat.format(flt.getDateFrom())+"' AND '"+outputFormat.format(flt.getDateTo())+"')  AND "+status+" = 1 ) \n";
                                     where =where +  " and NOT ('"+outputFormat.format(flt.getDateFrom())+"'  BETWEEN ISNULL(O1.DATE_ENTER,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"')) AND ISNULL(O1.DATE_LEAVE,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"')) AND '"+outputFormat.format(flt.getDateTo())+"'  BETWEEN ISNULL(O1.DATE_ENTER,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"')) AND ISNULL(O1.DATE_LEAVE,dateadd(day,1,'"+outputFormat.format(flt.getDateTo())+"')) AND "+status+" = 1)\n";

                }
            }

        }

        status =status.replace("VALUE","NULL");
        select = select + "," + status + " STATUS ";


        where =where + " AND (" + status +") IS NOT NULL";
        where =where + " AND (" + status +") in ("+CommonFunctions.ArrayToString(flt.getStatus())+")";


        if (flt.getStyle()!= null)
        {
            if  (flt.getStyle()[0].intValue()>0){
                where = where + " AND ROOM.STYLE_ID in ("+ CommonFunctions.ArrayToString(flt.getStyle()) +")";
            }
        }

        if (flt.getPlaces()!= null){
            if (!flt.getPlaces().equals("0") ){
                where = where + " AND ROOM.PLACES = " + flt.getPlaces() + " ";
            }
        }

        if (flt.getPrice1() >0){
            where = where + " AND COST >=" + flt.getPrice1();
        }

        if (flt.getPrice2() >0){
            where = where + " AND COST <=" + flt.getPrice2();
        }

        Query qr = entityManager.createNativeQuery(select+from+where,RoomSearchEntity.class) ;
        return qr.getResultList();
    }
     @SuppressWarnings("unchecked")
     public List<OrderCalendarEntity> getOrderCalendarList(Date dateFrom,Date dateTo,String roomId){


        SimpleDateFormat outputFormat = new SimpleDateFormat("yyyyMMdd");

        String sql ="SELECT DATEADD(day,Number-1,'"+outputFormat.format(dateFrom)+"') as DATE\n" +
                ",(select STATUS_ID from ORDERS where ROOM_ID="+roomId+" and DATEADD(day,Number-1,'"+outputFormat.format(dateFrom)+"') between DATE_ENTER and DATE_LEAVE) as STATUS\n" +
                "FROM Numbers\n" +
                "WHERE\n" +
                "Number <= DATEDIFF(day,'"+outputFormat.format(dateFrom)+"','"+outputFormat.format(dateTo)+"') + 1 \n" +
                "order by Number";

        Query qr = entityManager.createNativeQuery(sql, OrderCalendarEntity.class) ;
        return (List<OrderCalendarEntity>) qr.getResultList();
     }
}
